<?php

namespace App\services\v1;

use App\services\BaseServices;
use Illuminate\Support\Facades\Log;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Exception;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpParser\Error;

/*
 * 导出excel
 */

class OperationFileServices extends BaseServices
{

    /**
     * 1万条以内 复杂格式excel数据导出
     * @param array $data 数据
     * @param string $excelName excel名
     * @return bool
     * @throws Exception
     */
    public function exportExcel(array $data, string $excelName): bool
    {
        $spreedSheet = new Spreadsheet();
        $sheet = $spreedSheet->getActiveSheet();
        foreach ($data as $rowKey => $rowData) {
            $excelColumns = $rowKey + 1;
            $j = 0;
            foreach ($rowData as $field => $fieldData) {
                $sheet->setCellValue(strtoupper(chr(65 + $j)) . $excelColumns, $fieldData);
                $j++;
            }
        }
        $writer = new Xlsx($spreedSheet);
        $writer->save($excelName);
        return true;
    }


    /**
     * 大数据导出 todo 超过100w 分sheet excel每个sheet上限是100w
     * @param string $sql 查询结果集的sql
     * @param string $fileName 文件名 support: .xls .xlsx .doc .docx .csv
     * @param array $bomHeader 表头数组 ['id','name'...]
     * @param string $extension
     * @return void
     */
    public function exportCsvByArrayByIterator(string $sql, string $fileName, array $bomHeader = ['id'], string $extension = '.csv'): void
    {
        //让程序一直运行
        set_time_limit(0);

        header('Content-Encoding: UTF-8');
        switch ($extension) {
            case '.xls' :
            case '.csv' :
                Log::info($extension);
                header("Content-type:application/vnd.ms-excel;charset=UTF-8");
                break;
            //todo mac自带pages打不开, wps office可以正常打开
            case '.xlsx' :
                Log::info($extension);
                header('Content-type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8');
                break;
            case '.doc' :
                Log::info($extension);
                header('Content-type:application/octet-stream;charset=UTF-8');
                break;
            case '.docx' :
                Log::info($extension);
                header('Content-type:application/vnd.openxmlformats-officedocument.wordprocessingml.document;charset=UTF-8');
                break;
            default:
                throw new Error('File type error, support: .xls .xlsx .doc .docx .csv');
        }

        header('Content-Disposition: attachment;filename="' . $fileName.$extension.'"');
        //打开php标准输出流
        $fp = fopen('php://output', 'a');
        //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
        fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));

        //添加导出标题
        fputcsv($fp, $bomHeader);

        foreach ($this->arrayIterator($sql) as $item) {
            //向csv表格中添加每一行数据
            fputcsv($fp, $item);
        }
        fclose($fp);  //每生成一个文件关闭
        exit;
    }

    /**
     * 大数组生成器
     * @param $sql
     * @return \Generator
     */
    public function arrayIterator($sql): \Generator
    {
        //连接数据库
        $con = mysqli_connect(env('DB_HOST'), env('DB_USERNAME'), env('DB_PASSWORD'));
        //连接数据库报错信息
        if (!$con) {
            die('Could not connect: ' . mysqli_error($con));
        }
        mysqli_select_db($con, env('DB_DATABASE'));
        mysqli_query($con,'set names '.env('DB_CHARSET'));
        //该处用MYSQLI_USE_RESULT 就是不缓存结果集中，也是为了避免内存溢出，相当于mysql_unbuffered_query
        foreach (mysqli_query($con, $sql,MYSQLI_USE_RESULT) as $row ){
            yield $row;
        }
        $con->close();
    }

    public function operationExcelLog()
    {
    }

}